package MySQLConnection;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.UUID;

import model.BusinessCase;

import common.Exceptions.DAOException;

import DAO.BcDAO;

public class MySQLBcDAO implements BcDAO 
{
	private final static String TABLE_NAME = MySqlDAOFactory.DBName + "." + "businesscase"; 
	private static MySQLBcDAO instance;
	
	public static MySQLBcDAO getInstance()
	{
		if(instance == null)
			instance = new MySQLBcDAO();
		
		return instance;
	}
	
	@Override
	public void insert(BusinessCase item) throws DAOException 
	{
		String query = "INSERT INTO " +  TABLE_NAME + "(bc_id, bc_name, bc_domain, bc_subDomain, bc_challenge, bc_conclusion, bc_author, bc_experts, bc_decisionMaker, bc_perspective, sol_id, bc_createdDate, bc_lastUpdated)" +
				"VALUES(?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

		try 
		{
			PreparedStatement preStatement = MySqlDAOFactory.getInstance().prepareStatement(query);
			
			preStatement.setString(1, item.getID().toString());
			preStatement.setString(2, item.getName());
			preStatement.setString(3, item.getDomain());
			preStatement.setString(4, item.getSubDomain());
			preStatement.setString(5, item.getChallenge());
			preStatement.setString(6, item.getConclusion());
			preStatement.setString(7, item.getAuthor());
			preStatement.setString(8, item.getExperts());
			preStatement.setString(9, item.getDecisionMaker());
			preStatement.setString(10, item.getPerspective());
			
			if(item.getSolution() != null)
				preStatement.setString(11, item.getSolID().toString());
			
			preStatement.setTimestamp(12, new java.sql.Timestamp(new java.util.Date().getTime()));
			preStatement.setTimestamp(13, new java.sql.Timestamp(new java.util.Date().getTime()));
			
			preStatement.executeUpdate();
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The insert method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
	}

	@Override
	public void update(BusinessCase item) throws DAOException
	{
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE bc_id = '" + item.getID() + "';";
		
		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			if(dataSet.next())
			{
				String bcID = dataSet.getString("bc_id");
				if(bcID != null)
				{
					String updateQuery = "UPDATE " + TABLE_NAME + " SET bc_name = ?, bc_Domain = ?, bc_subDomain = ?, bc_challenge = ?, " +
							"bc_conclusion = ?, bc_author = ?, bc_experts = ?, bc_decisionMaker = ?, bc_perspective = ?, sol_id = ?, bc_lastUpdated = ? WHERE bc_id = ?";

					PreparedStatement preStatement = MySqlDAOFactory.getInstance().prepareStatement(updateQuery);
					preStatement.setString(1, item.getName());
					preStatement.setString(2, item.getDomain());
					preStatement.setString(3, item.getSubDomain());
					preStatement.setString(4, item.getChallenge());
					preStatement.setString(5, item.getConclusion());
					preStatement.setString(6, item.getAuthor());
					preStatement.setString(7, item.getExperts());
					preStatement.setString(8, item.getDecisionMaker());
					preStatement.setString(9, item.getPerspective());
					
					if(item.getSolID() != null)
						preStatement.setString(10, item.getSolID().toString());
					else
						preStatement.setNull(10, java.sql.Types.VARCHAR);
					
					preStatement.setTimestamp(11, new java.sql.Timestamp(new java.util.Date().getTime()));
					preStatement.setString(12, bcID.toString());
				
					preStatement.executeUpdate();
						
					if(dataSet.next())
						System.err.println("WARING: Two or more Business Cases with the same ID were found in the database. bc_id = " + item.getID());
				}
			}
			else
				throw new DAOException("The Business Case identified by the id " + item.getID() + "hasn't been found in the database.");
			
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The update method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
	}

	@Override
	public void delete(UUID id) throws DAOException 
	{
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE bc_id = '" + id + "';";

		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			if(dataSet.next())
			{
				String deleteQuery = "DELETE FROM " + TABLE_NAME + " WHERE bc_id = '" + id + "'";
				
				statement = MySqlDAOFactory.getInstance().createStatement();
				if(statement.executeUpdate(deleteQuery) == 0)
					throw new DAOException("The " + TABLE_NAME +" identified by the id " + id + " couldn't be deleted due to an unknown error.");
					
				if(dataSet.next())
					System.err.println("WARING: Two or more " + TABLE_NAME +" with the same ID were found in the database. bc_id = " + id);
			}
			else
				throw new DAOException("The " + TABLE_NAME + " identified by the id " + id + " hasn't been found in the database.");
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The delete method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
	}

	@Override
	public List<BusinessCase> getAll() throws DAOException 
	{
		String query = "SELECT * FROM " + TABLE_NAME + ";";
		List<BusinessCase> bcCases = new LinkedList<BusinessCase>();
		boolean lockOwner = false;

		try 
		{
			lockOwner = MySqlDAOFactory.lockConnection.tryLock();
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			while(dataSet.next())
			{
				UUID bcID = UUID.fromString(dataSet.getString("bc_id"));
				BusinessCase bc = getByID(bcID);
				bcCases.add(bc);
			}
			
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The getAll method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			if(lockOwner)
			{
				MySqlDAOFactory.lockConnection.unlock();
				MySqlDAOFactory.getInstance().closeConnection();
			}
		}
		
		return bcCases;
	}

	@Override
	public List<BusinessCase> getCountMostRecentlyEditedBcs(int count) throws DAOException 
	{
		String query = "SELECT * FROM " + TABLE_NAME + " order by bc_lastUpdated asc LIMIT " + count + ";";
		
		List<BusinessCase> bcCases = new LinkedList<BusinessCase>();
		boolean lockOwner = false;

		try 
		{
			lockOwner = MySqlDAOFactory.lockConnection.tryLock();
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			while(dataSet.next())
			{
				UUID bcID = UUID.fromString(dataSet.getString("bc_id"));
				BusinessCase bc = getByID(bcID);
				bcCases.add(bc);
			}
			
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The getAll method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			if(lockOwner)
			{
				MySqlDAOFactory.lockConnection.unlock();
				MySqlDAOFactory.getInstance().closeConnection();
			}
		}
		
		return bcCases;
	}
	
	@Override
	public BusinessCase getByID(UUID id) throws DAOException 
	{
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE bc_id = '" + id + "';";
		BusinessCase bc = null;
		
		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			if(dataSet.next())
			{
				UUID bcID = UUID.fromString(dataSet.getString("bc_id"));
				String bcName = dataSet.getString("bc_name");
				String bcDomain = dataSet.getString("bc_domain");
				String bcSubDomain = dataSet.getString("bc_subDomain");
				String bcChallenge = dataSet.getString("bc_challenge");
				String bcConclusion= dataSet.getString("bc_conclusion");
				String bcAuthor = dataSet.getString("bc_author");
				String bcExperts = dataSet.getString("bc_experts");
				String bcDecisionMaker = dataSet.getString("bc_decisionMaker");
				String bcPerspective = dataSet.getString("bc_Perspective");
				String solID = dataSet.getString("sol_id");
				Date createdDate = new Date(dataSet.getTimestamp("bc_createdDate").getTime());
				Date updatedDate = new Date(dataSet.getTimestamp("bc_lastUpdated").getTime());
				
				bc = new BusinessCase(bcID);
				bc.setName(bcName);
				bc.setDomain(bcDomain);
				bc.setAuthor(bcAuthor);
				bc.setSubDomain(bcSubDomain);
				bc.setChallenge(bcChallenge);
				bc.setConclusion(bcConclusion);
				bc.setExperts(bcExperts);
				bc.setDecisionMaker(bcDecisionMaker);
				bc.setPerspective(bcPerspective);
				bc.setSolID(UUID.fromString(solID));
				bc.setCreatedDate(createdDate);
				bc.setLastUpdated(updatedDate);
				
				if(dataSet.next())
					System.err.println("WARING: Two or more Business Cases with the same ID were found in the database. BC_id = " + id);
			}
			else
				throw new DAOException("The Business Case identified by the id " + id + "hasn't been found in the database.");
			
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The getByID method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
		
		return bc;
	}
}
